--USE THIS AND THE TOP LEVEL "XML Schema Elements" for reference in the msdn.
--http://msdn.microsoft.com/en-us/library/ms256118(v=vs.90).aspx

DECLARE @XML XML = 
					(
						SELECT
							TOP 10 
							M.memid AS "@memid",
							LTRIM(RTRIM(ET.firstname)) AS "FirstName",
							LTRIM(RTRIM(ET.lastname)) AS "LastName",
							LTRIM(RTRIM(E.[description])) AS "Ethnicity"
						FROM
							LS_QNXT.plandata_parallel.dbo.member AS M
							INNER JOIN LS_QNXT.plandata_parallel.dbo.ethnicity AS E ON (M.ethnicid = E.ethnicid)
							INNER JOIN LS_QNXT.plandata_parallel.dbo.entity AS ET ON (M.entityid = ET.entid) 
						WHERE
							RTRIM(LTRIM(M.memid)) <> ''
						FOR XML PATH('member'), ROOT('members')
					)
SELECT @XML AS Results		

------Creating a table with an XML field bound by a schema collection
IF OBJECT_ID('healthpas_reports.dbo.XMLTestTable') IS NOT NULL DROP TABLE healthpas_reports.dbo.XMLTestTable

CREATE TABLE healthpas_common.dbo.XMLTestTable (ID INT PRIMARY KEY IDENTITY(1,1),XmlField XML(dbo.MemberSchema))

INSERT INTO healthpas_common.dbo.XMLTestTable (XmlField) VALUES (@XML)


--*****VALID XML SCHEMA COLLECTION*****

--Creating an XML Schema Collection
CREATE XML SCHEMA COLLECTION dbo.MemberSchema AS N'
	<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
		<xsd:element name="members">
			<xsd:complexType>
				<xsd:sequence>
					<xsd:element name="member" minOccurs="0" maxOccurs="unbounded">
						<xsd:complexType>
							<xsd:sequence>
								<xsd:element name="FirstName"/>
								<xsd:element name="LastName" type="xsd:string"/>
								<xsd:element name="Ethnicity" />
							</xsd:sequence>
							<xsd:attribute name="memid" />
						</xsd:complexType>
					</xsd:element>
				</xsd:sequence>
			</xsd:complexType>
		</xsd:element>
	</xsd:schema>'


--Dropping and XML Schema Collection
DROP XML SCHEMA COLLECTION MemberSchema

--Registration of the Schema Collections
SELECT * FROM sys.xml_schema_collections








